/**
 * 
 */
package model.admin;

import java.sql.ResultSet;
import java.util.ArrayList;

import myutil.Connect;
import vo.BookBorrowVo;
import vo.LibraryBookDetailsVo;
import vo.mixed.LibraryStudentVo;

/**
 * @author Sony
 *
 */
public class LibraryMo {
	ArrayList<LibraryStudentVo> list = new ArrayList<LibraryStudentVo>();
	ArrayList<LibraryBookDetailsVo> bookCategories = new ArrayList<LibraryBookDetailsVo>();

	public ArrayList<LibraryBookDetailsVo> bookList() {
		Connect c = new Connect();
		String queryToGetLibraryBooks = "select category,count(*) as count from library_book_details group by category";
		try {
			ResultSet rs = c.getResult(queryToGetLibraryBooks);
			while (rs.next()) {
				LibraryBookDetailsVo voObj=new LibraryBookDetailsVo();
				voObj.setCategory(rs.getString("category"));
				voObj.setCount(rs.getInt("count"));
				bookCategories.add(voObj);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return bookCategories;
	}
	
	public ArrayList<LibraryBookDetailsVo> allBooksList(String category) {
		Connect c = new Connect();
		String queryToGetLibraryBooks = "select count(*) as count,book_name,description,author_name,category,book_id,price,publisher,referred_by from library_book_details where category='"+category+"' group by book_name";
		System.out.println(queryToGetLibraryBooks);
		try {
			ResultSet rs = c.getResult(queryToGetLibraryBooks);
			while (rs.next()) {
				LibraryBookDetailsVo vo=new LibraryBookDetailsVo();
				vo.setBook_name(rs.getString("book_name"));
				vo.setDescription(rs.getString("description"));
				vo.setAuthor_name(rs.getString("author_name"));
				vo.setCategory(rs.getString("category"));
				vo.setBook_id(rs.getInt("book_id"));
				vo.setPrice(rs.getInt("price"));
				vo.setPublisher(rs.getString("publisher"));
				vo.setReferred_by(rs.getString("referred_by"));
				vo.setCount(rs.getInt("count"));
				System.out.println("Book name : "+rs.getString("book_name")+" Count is : "+rs.getInt("count"));
				bookCategories.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return bookCategories;
	}
	public ArrayList<LibraryBookDetailsVo> allBooks() {
		Connect c = new Connect();
		String queryToGetLibraryBooks = "select count(*) as count,book_name,description,author_name,category,book_id,price,publisher,referred_by from library_book_details group by book_name";
		try {
			ResultSet rs = c.getResult(queryToGetLibraryBooks);
			while (rs.next()) {
				LibraryBookDetailsVo vo=new LibraryBookDetailsVo();
				vo.setBook_name(rs.getString("book_name"));
				vo.setDescription(rs.getString("description"));
				vo.setAuthor_name(rs.getString("author_name"));
				vo.setCategory(rs.getString("category"));
				vo.setBook_id(rs.getInt("book_id"));
				vo.setPrice(rs.getInt("price"));
				vo.setPublisher(rs.getString("publisher"));
				vo.setReferred_by(rs.getString("referred_by"));
				vo.setCount(rs.getInt("count"));
				bookCategories.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return bookCategories;
	}
	
	public void AddDetails(BookBorrowVo bv) throws Exception {
		System.out.println(bv.getBorrower_id()+" : "+bv.getBook_id()+" : "+bv.getBorrower_type());
		Connect c = new Connect();
		ResultSet rs=null,rs1=null;

		rs=c.getResult("select * from student_book_borrow where book_id="+bv.getBook_id()+" and returned=0");
		rs1=c.getResult("select * from staff_book_borrow where book_id="+bv.getBook_id()+" and returned=0");
		if(rs.next())
		{
			throw new Exception("Book already issued and not returned");
		}
		if(rs1.next())
		{
			throw new Exception("Book already issued and not returned");
		}
		String queryToAddIssuedBookDetails = "", insertInto = null;
		if (bv.getBorrower_type().equals("student")) {
			insertInto = "insert into student_book_borrow";
		} else if (bv.getBorrower_type().equals("staff")) {
			insertInto = "insert into staff_book_borrow";
		}
		queryToAddIssuedBookDetails = insertInto
				+ "(borrower_id,book_id,issue_date) values ("
				+ bv.getBorrower_id() + "," + bv.getBook_id() + ",NOW())";
		try{	
		//System.out.println(queryToAddIssuedBookDetails);
		c.dml(queryToAddIssuedBookDetails);
		c.logger(bv.getBorrower_type(), "Insert");
		}catch(Exception ex)
		{
			throw new Exception("Invalid data entered..");
		}
	}
	public ArrayList<LibraryStudentVo> GetIssuedBookList(String view) {
		String queryToGetIssuedBookListStudent = null;
		String queryToGetIssuedBookListStaff = null;
		if (view.equals("issuedbooks")) {
			queryToGetIssuedBookListStudent = "select student_name,borrower_id,lbd.book_id,book_name,issue_date,return_date,returned,fine from student_book_borrow sbb,library_book_details lbd,student_details where sbb.book_id=lbd.book_id and student_id=borrower_id and returned=0";
			queryToGetIssuedBookListStaff = "select staff_name,borrower_id,lbd.book_id,book_name,issue_date,return_date,returned,fine from staff_book_borrow sbb,library_book_details lbd,college_staff where sbb.book_id=lbd.book_id and staff_id=borrower_id and returned=0";
		} else if (view.equals("all")) {
			queryToGetIssuedBookListStudent = "select student_name,borrower_id,lbd.book_id,book_name,issue_date,return_date,returned,fine from student_book_borrow sbb,library_book_details lbd,student_details where sbb.book_id=lbd.book_id and student_id=borrower_id";
			queryToGetIssuedBookListStaff = "select staff_name,borrower_id,lbd.book_id,book_name,issue_date,return_date,returned,fine from staff_book_borrow sbb,library_book_details lbd,college_staff where sbb.book_id=lbd.book_id and staff_id=borrower_id";
		}
		Connect c = new Connect();

		try {
			ResultSet rs = c.getResult(queryToGetIssuedBookListStudent);
			ResultSet rs1 = c.getResult(queryToGetIssuedBookListStaff);
			while (rs.next()) {
				LibraryStudentVo vo = new LibraryStudentVo();
				vo.setStudent_name(rs.getString("student_name"));
				vo.setBorrower_id(rs.getInt("borrower_id"));
				vo.setBook_id(rs.getInt("book_id"));
				vo.setIssue_date(rs.getDate("issue_date")+"");
				vo.setReturn_date(rs.getDate("return_date")+"");
				vo.setFine(rs.getInt("fine"));
				vo.setBook_name(rs.getString("book_name"));
				vo.setBorrower_type("student");
				vo.setReturned(rs.getInt("returned"));
				list.add(vo);
			}
			while (rs1.next()) {
				LibraryStudentVo vo = new LibraryStudentVo();
				vo.setStudent_name(rs1.getString("staff_name"));
				vo.setBorrower_id(rs1.getInt("borrower_id"));
				vo.setBook_id(rs1.getInt("book_id"));
				vo.setIssue_date(rs1.getDate("issue_date")+"");
				vo.setReturn_date(rs1.getDate("return_date")+"");
				vo.setFine(rs1.getInt("fine"));
				vo.setBook_name(rs1.getString("book_name"));
				vo.setBorrower_type("staff");
				vo.setReturned(rs1.getInt("returned"));
				list.add(vo);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;

	}

	public ArrayList<LibraryStudentVo> GetFilteredIssuedBookList(
			String yearfilter, String coursefilter) {
		// TODO Auto-generated method stub
		return null;
	}

	public void returned(int book_id,String borrower_type,String type) throws Exception
	{
		Connect c=new Connect();
		String query = null;
		if(borrower_type.equals("student"))
		{
			query="update student_book_borrow set returned=1,return_date=now() where book_id="+book_id;
			//System.out.println("Query : "+query);
		}
		else if(borrower_type.equals("staff"))
		{
			query="update staff_book_borrow set returned=1,return_date=now() where book_id="+book_id;
		}
		c.dml(query);
		if(borrower_type.equals("student")){
			c.logger(type, "Update");
		}else{
			c.logger(type, "update");
		}
	}
	
	public void addBook(LibraryBookDetailsVo vo,String type) {
		ArrayList<String> title = new ArrayList<String>(),
				author = new ArrayList<String>(),
				description = new ArrayList<String>(),
				numberOfBooks = new ArrayList<String>(),
				price = new ArrayList<String>(),
				referredby = new ArrayList<String>(),
				publisher = new ArrayList<String>(),
				category = new ArrayList<String>();
		
		System.out.println("ARRAYLIST IN MO : ");
		Connect c = new Connect();
		// String query = null;
		title = vo.getBook_name_list();
		author = vo.getAuthor_name_list();
		category = vo.getCategory_list();
		numberOfBooks = vo.getCount_list();
		description = vo.getDescription_list();
		price = vo.getPrice_list();
		referredby = vo.getReferred_by_list();
		publisher = vo.getPublisher_list();

		for (int i = 0; i < title.size(); i++) {
			for (int j = 0; j < Integer.parseInt(numberOfBooks.get(i)); j++) {

				String query = "insert into library_book_details(book_name,author_name,category,description,price,referred_by,publisher) values('"
						+ title.get(i)
						+ "','"
						+ author.get(i)
						+ "','"
						+ category.get(i)
						+ "','"
						+ description.get(i)
						+ "',"
						+ price.get(i)
						+ ",'"
						+ referredby.get(i)
						+ "','"
						+ publisher.get(i) + "')";
				try {

					c.dml(query);
					c.logger(type, "Insert");
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}

			}

		}
	}
	/*public ArrayList<LibraryStudentVo> GetFilteredIssuedBookList(String yearfilter, String coursefilter) {
		String query="";
		Connect c=new Connect();
		try {
			if(yearfilter.equals("all")&&coursefilter.equals("all"))
			{
				query="select * from ";
			}
			ResultSet rs=c.getResult(query);
			
			
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	} */
	public ArrayList<LibraryBookDetailsVo> edit(int book_id) {
		Connect c = new Connect();
		String query = "select * from library_book_details where book_id="
				+ book_id;
		System.out.println(query);
		try {
			ResultSet rs = c.getResult(query);
			while (rs.next()) {
				LibraryBookDetailsVo vo = new LibraryBookDetailsVo();
				vo.setBook_name(rs.getString("book_name"));
				vo.setDescription(rs.getString("description"));
				vo.setAuthor_name(rs.getString("author_name"));
				vo.setCategory(rs.getString("category"));
				vo.setBook_id(book_id);
				vo.setPrice(rs.getInt("price"));
				vo.setPublisher(rs.getString("publisher"));
				vo.setReferred_by(rs.getString("referred_by"));
				bookCategories.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bookCategories;
	}

	public void del(int book_id,String type) throws Exception {
		Connect c = new Connect();
		String query = "delete from library_book_details where book_id="
				+ book_id;
		String query2 = "select * from student_book_borrow where book_id="
				+ book_id + " and returned=0";
		String query3 = "select * from staff_book_borrow where book_id="
				+ book_id + " and returned=0";

		ResultSet rs = c.getResult(query2);
		
		while (rs.next()) {
			System.out.println("Book issued by student and not yet returned");
			throw new Exception("Book issued by student and not yet returned");
		}

		//System.out.println("shouldn't come here............!!");
		
		rs = c.getResult(query3);
		while (rs.next()) {
			System.out.println("Book issued by staff and not yet returned");
			throw new Exception("Book issued by staff and not yet returned");
		}
		
		try {
			c.dml(query);
			c.logger(type, "Delete");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ArrayList<LibraryBookDetailsVo> getCategories() {
		// TODO Auto-generated method stub

		Connect c = new Connect();
		String query = "select category from library_book_details group by category";
		ResultSet rs;
		try {
			rs = c.getResult(query);

			while (rs.next()) {
				LibraryBookDetailsVo vo = new LibraryBookDetailsVo();
				vo.setCategory(rs.getString("category"));
				bookCategories.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bookCategories;
	}

	public void submit(LibraryBookDetailsVo vo,String type) {
		// TODO Auto-generated method stub
		Connect c = new Connect();
		System.out.println("came to submit in mo");
		String query = "update library_book_details set book_name='"
				+ vo.getBook_name() + "',author_name='" + vo.getAuthor_name()
				+ "',description='" + vo.getDescription() + "',category='"
				+ vo.getCategory() + "',price=" + vo.getPrice()
				+ ",referred_by='" + vo.getReferred_by() + "',publisher='"
				+ vo.getPublisher() + "' where book_id=" + vo.getBook_id();
		try {
			c.dml(query);
			c.logger(type, "Update");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void updateCategory(String oldcategory,String category,String type) {
		Connect c = new Connect();
		String query="update library_book_details set category='"+category+"' where category='"+oldcategory+"'";
		try {
			c.dml(query);
			c.logger(type, "Update");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}

	public ArrayList<LibraryStudentVo> allIndividualBooks(String book_name) {
		Connect c = new Connect();
		String query2="",query3="";
		int issued=0,borrower_id=0;
		String issue_date="",borrower_type="";
		String roll_number;
		String queryToGetLibraryBooks = "select book_name,description,author_name,category,book_id,price,publisher,referred_by from library_book_details where book_name='"+ book_name +"'";
		//System.out.println(queryToGetLibraryBooks);
		try {
			
			ResultSet rs = c.getResult(queryToGetLibraryBooks);
			ResultSet rs1;
			ResultSet rs2;
			while (rs.next()) {
				issue_date="";
				borrower_type="";
				borrower_id=0;
				issued=0;
				LibraryStudentVo vo=new LibraryStudentVo();
				query2="select * from student_book_borrow where book_id="+rs.getInt("book_id")+" and returned=0";
				query3="select * from staff_book_borrow where book_id="+rs.getInt("book_id")+" and returned=0";
				rs1=c.getResult(query2);
				rs2=c.getResult(query3);
				//System.out.println(query2);
				//System.out.println(query3);
		try{
				while(rs1.next())
				{
					//System.out.println("book"+rs1.getInt("book_id")+" borrowed by student");
					borrower_id=rs1.getInt("borrower_id");
					borrower_type="Student";
					issued=1;
					issue_date=rs1.getDate("issue_date")+"";
				}
			}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				try{
				while(rs2.next())
				{
					//System.out.println("book"+rs2.getInt("book_id")+" borrowed by staff");
					borrower_id=rs2.getInt("borrower_id");
					borrower_type="Staff";
					issued=1;
					issue_date=rs2.getDate("issue_date")+"";
				}
			}catch(Exception e)
			{
				e.printStackTrace();
			} 
				vo.setBorrower_type(borrower_type);
				vo.setBorrower_id(borrower_id);
				vo.setIssued(issued);
				vo.setIssue_date(issue_date);
				vo.setBook_id(rs.getInt("book_id"));
				vo.setBook_name(rs.getString("book_name"));
				vo.setDescription(rs.getString("description"));
				vo.setAuthor_name(rs.getString("author_name"));
				vo.setCategory(rs.getString("category"));
				vo.setPrice(rs.getInt("price"));
				vo.setPublisher(rs.getString("publisher"));
				vo.setReferred_by(rs.getString("referred_by"));
				//System.out.println("book_id="+rs.getInt("book_id"));
				list.add(vo);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
}
